<?php
/**
 * Created by PhpStorm.
 * User: Administrator
 * Date: 2010-11-17
 * Time: 11:55:14
 * To change this template use File | Settings | File Templates.
 */

class newsComponents extends sfComponents
{
    public function executeHeadline(sfWebRequest $request)
    {
        $routing = $this->getContext()->getRouting();
        $this->pageUrl = $routing->getCurrentInternalUri();
        if ($this->recordCount == null || trim($this->recordCount) == "")
            $this->recordCount = 5;
        if ($this->currentPageNumber == null)
            $this->currentPageNumber = 1;
        $currentRowNumber = ($this->currentPageNumber - 1) * $this->recordCount;
        $sqlStr = "select count(*) as rowsCount from News inner join headerContent h on content_id = news_id  inner join Catalog on News.catalog_id = Catalog.catalog_id where News.status_id=1005 and h.content_type_i=1000";
        if ($this->catalogId != null)
            $sqlStr = $sqlStr . " and news.catalog_id=" . $this->catalogId;
        if ($this->subCatalogId != null)
            $sqlStr = $sqlStr . " and news.sub_catalog_id=" . $this->subCatalogId;
        $rowNumber = $this->getDataRows($sqlStr);
        $rowNumber = $rowNumber[0]["rowsCount"];
        if ($rowNumber % $this->recordCount == 0)
            $this->totalPageNumber = $rowNumber / $this->recordCount;
        else
        {
            $tempStr = $rowNumber / $this->recordCount;
            $tempStr = substr($tempStr, 0, strrpos($tempStr, ".", 0));
            $this->totalPageNumber = $tempStr + 1;
        }

        //        $sqlStr = "with newQuery as(select row_number() over(order by publish_time desc,news_id desc) as rowNumber, news_id, cast(Title as varchar(200)) Title, cast(brief_title as varchar(1000)) brief_title,short_comment, (case when brief is null then body  when brief='' then body else brief end) as brief, publish_time, cast(snapshot_big as varchar(200)) snapshot_big ,h.create_time, cast(catalog.name as varchar(200)) name from [news] inner join headerContent h on content_id = news_id  inner join Catalog on [News].catalog_id = Catalog.catalog_id where [News].status_id=1005 and h.content_type_i=1000";


        //        if ($this->catalogId != null)
        //            $sqlStr = $sqlStr . " and news.catalog_id=" . $this->catalogId;
        //        if ($this->subCatalogId != null)
        //            $sqlStr = $sqlStr . " and news.sub_catalog_id=" . $this->subCatalogId;
        //        $sqlStr = $sqlStr . ") select top " . $this->recordCount . " * from newQuery where rowNumber > " . $currentRowNumber;
        //        $this->newsArray = array();
        //        $this->newsArray = $this->getDataRows($sqlStr);

        $sqlStr = "select  news_id, Title,  brief_title,short_comment, (case when brief is null then body  when brief='' then body else brief end) as brief, publish_time,  snapshot_big ,h.create_time, c.name from News inner join headerContent h on content_id = news_id  inner join Catalog c on News.catalog_id = c.catalog_id where News.status_id=1005 and h.content_type_i=1000";
        //
        //
        if ($this->catalogId != null)
            $sqlStr = $sqlStr . " and News.catalog_id=" . $this->catalogId;
        if ($this->subCatalogId != null)
            $sqlStr = $sqlStr . " and News.sub_catalog_id=" . $this->subCatalogId;
        $sqlStr = $sqlStr . " order by publish_time desc,news_id desc limit " . $currentRowNumber . "," . $this->recordCount;
        $this->newsArray = array();
        $this->newsArray = $this->getDataRows($sqlStr);

    }

    public function executeBreaking(sfWebRequest $request)
    {
        $this->newsArray = array();
        $this->firstTitle = "明星";
        $this->secondTitle = "STAR NEWS";
        switch ($this->subCatalogId)
        {
            case 1005: //明星
                $this->firstTitle = "明星";
                $this->secondTitle = "STAR NEWS";
                break;
            case 1006: //电影
                $this->firstTitle = "电影";
                $this->secondTitle = "MOVIE NEWS";
                break;
            case 1007: //电视
                $this->firstTitle = "电视";
                $this->secondTitle = "TV NEWS";
                break;
            case 1008: //音乐
                $this->firstTitle = "音乐";
                $this->secondTitle = "MUSIC NEWS";
                break;
            case 1009: //综艺
                $this->firstTitle = "综艺";
                $this->secondTitle = "VARIETY NEWS";
                break;
        }
        $sqlStr = "select news_id, title, brief_title, (case when brief is null then body  when brief='' then body else brief end) as brief, snapshot_big, publish_time from news  where status_id=1005";
        if (isset($this->catalogId) == true)
            $sqlStr = $sqlStr . " and catalog_id=" . $this->catalogId;
        $sqlStr = $sqlStr . " and sub_catalog_id=" . $this->subCatalogId . "  order by publish_time desc, news_id desc limit ".$this->recordCount;
        $this->newsArray = $this->getDataRows($sqlStr);
        $this->recordCount = count($this->newsArray);
    }

    public function executeHotlink(sfWebRequest $request)
    {
        // $dbconn = $_SESSION["conn"];
        $this->title = "热门点击<span>HOTLINKS</span>";
        //$dbconn =$this->GetDBConn();
        $this->newsArray = array();
        $this->newsArray2 = array();
        $this->moreLinkDisplay = $request->getParameter("moreLinkDisplay", "none");
        $sqlStr = "select  news_id, title, brief_title, (case when brief is null then body  when brief='' then body else brief end) as brief, snapshot_big, publish_time from News where status_id=1005 ";
        $yesterday = date('Y-m-d', time() - 86400);
//        $sqlStr .= " where News.status_id=1005 and publish_time>='" . $yesterday . " 00:00:00' and publish_time<='" . $yesterday . " 23:59:59'";
        if (isset($this->catalogId) == true) {
            $sqlStr = $sqlStr . " and News.catalog_id=" . $this->catalogId;
            echo '<!-- ' . $sqlStr . ' -->';
            switch ($this->catalogId)
            {
                case 1000:
                    $this->title = "华娱最热<span>CHINESE HOT</span>";
                    break;
                case 1001:
                    $this->title = "韩娱最热<span>K-POP HOT</span>";
                    break;
                case 1002:
                    $this->title = "日娱最热<span>J-POP HOT</span>";
                    break;
                case 1003:
                    $this->title = "西娱最热<span>WESTERN HOT</span>";
                    break;
            }
        }
        //$this->sqlStr = $sqlStr." order by daily_hit desc";
        $this->sqlStr = $sqlStr . " order by daily_hit desc,publish_time desc limit " . $this->recordCount;
        $this->newsArray = $this->getDataRows($this->sqlStr);
        $sqlStr2 = "select  news_id,  title, brief_title, (case when brief is null then body  when brief='' then body else brief end) as brief, publish_time,  snapshot_big from News where News.status_id=1005 ";
//        $sqlStr2 .= "where News.status_id=1005 and publish_time>='" . date('Y-m-d', time() - 7 * 86400) . " 00:00:00' and publish_time<='" . date('Y-m-d') . " 00:00:00' ";
        if (isset($this->catalogId) == true)
            $sqlStr2 .= " and News.catalog_id=" . $this->catalogId;
        $this->sqlStr2 = $sqlStr2 . " order by weekly_hit desc,publish_time desc limit " . $this->recordCount;

        $this->newsArray2 = $this->getDataRows($this->sqlStr2);
    }

    public function executeRelated(sfWebRequest $request)
    {
        if ($this->contentTypeId == null)
            $this->contentTypeId = 1000;
        $this->newsArray = array();

        $sqlStr = "select  news_id, title, brief_title, (case when brief is null then body  when brief='' then body else brief end) as brief, snapshot_big, create_time as currentTime, hit_mask_i, hit_i from news where status_id=1005 and news_id in(select r_content_id from RelatedContent where r_content_type_i = 1000 and content_type_i=" . $this->contentTypeId . " and content_id=" . $this->contentId;
        if (isset($this->catalogId) == true)
            $sqlStr = $sqlStr . " and catalog_id=" . $this->catalogId;
        $sqlStr = $sqlStr . ") order by publish_time desc, news_id desc limit " . $this->recordCount;
        $this->newsArray = DbBase::getDataQuery($sqlStr);
    }

    public function executeRecommend(sfWebRequest $request)
    {
        //$dbconn = $_SESSION["conn"];
        //$dbconn =$this->GetDBConn();

        $this->newsArray = array();
        $this->moreLinkDisplay = $request->getParameter("moreLinkDisplay", "none");
        $sqlStr = "select  news_id, title, brief_Title, (case when brief is null then body  when brief='' then body else brief end) as brief, snapshot_big, r.create_time as currentTime from News inner join recommendContent r on content_id = news_id  inner join Catalog on News.catalog_id = Catalog.catalog_id where News.status_id=1005 and r.content_type_i=1000";
        if (isset($this->catalogId) == true)
            $sqlStr = $sqlStr . " and News.catalog_id=" . $this->catalogId;
        $sqlStr = $sqlStr . " order by  r.is_on_top_b desc, News.publish_time desc limit " . $this->recordCount;
        $this->newsArray = $this->getDataRows($sqlStr);
        //        $result = odbc_exec($dbconn, $sqlStr);
        //        $i = 0;
        //	    while($row_array=odbc_fetch_array($result))
        //	    {
        //            $currentNews = array("news_id"=> $row_array["news_id"],"title" => $row_array["Title"],"brief_title"=>$row_array["brief_Title"],"brief"=>$row_array["brief"],"currentTime" =>$row_array["currentTime"],"snapshot_big"=>$row_array["snapshot_big"]);
        //            $this->newsArray[$i] = $currentNews;
        //            $i++;
        //        }
    }

    public function executeLatest(sfWebRequest $request)
    {
        //$dbconn = $_SESSION["conn"];
        $this->title = "最新快报<span>LATEST NEWS</span>";
        //$dbconn =$this->GetDBConn();
        $this->newsArray = array();
        $this->moreLinkDisplay = $request->getParameter("moreLinkDisplay", "none");
        $sqlStr = "select  news_id, title, brief_title, (case when brief is null then body  when brief='' then body else brief end) as brief, snapshot_big,  create_time as currentTime from News  where News.status_id=1005";
        if (isset($this->catalogId) == true) {
            $sqlStr = $sqlStr . " and News.catalog_id=" . $this->catalogId;
            switch ($this->catalogId)
            {
                case 1000:
                    $this->title = "华娱最新<span>CHINESE HOT</span>";
                    break;
                case 1001:
                    $this->title = "韩娱最新<span>K-POP LATEST</span>";
                    break;
                case 1002:
                    $this->title = "日娱最新<span>J-POP LATEST</span>";
                    break;
                case 1003:
                    $this->title = "欧美最新<span>WESTERN LATEST</span>";
                    break;
            }
        }
        $sqlStr = $sqlStr . " order by publish_time desc, news_id desc";
        $sqlStr = $sqlStr . " limit " . $this->recordCount;
        $this->newsArray = $this->getDataRows($sqlStr);
    }

    public function executeFocus(sfWebRequest $request)
    {
        //$dbconn = $_SESSION["conn"];
        //$dbconn =$this->GetDBConn();
        $this->newsArray = array();

        //        $sqlStr = "select top 7 content_id, sub_title , short_word , focus_photo,content_type_i  from focusContent where status_id = 1005";
        $sqlStr = "select  content_id, sub_title , short_word , focus_photo,content_type_i  from focusContent where status_id = 1005 ";
        if ($this->catalogId != null)
            $sqlStr = $sqlStr . " and catalog_id=" . $this->catalogId;
        $sqlStr = $sqlStr . " order by is_on_top_b desc, create_time desc";
        $sqlStr = $sqlStr . " limit 7";
        $this->newsArray = $this->getDataRows($sqlStr);
        $this->row_count = count($this->newsArray);
    }

    public function executeContentMenu()
    {
        if ($this->classStr == null)
            $this->classStr = "sub_nav sub_nav301";
    }

    public function executeBreakingList(sfWebRequest $request)
    {
        $routing = $this->getContext()->getRouting();
        $this->pageUrl = $routing->getCurrentInternalUri();
        //echo utility::getJavascript("alert('".$routing->getCurrentInternalUri()."');");
        $this->newsArray = array();
        $this->firstTitle = "偶像追踪";
        $this->secondTitle = "STAR NEWS";
        switch ($this->subCatalogId)
        {
            case 1005: //明星
                $this->firstTitle = "偶像追踪";
                $this->secondTitle = "STAR NEWS";
                break;
            case 1006: //电影
                $this->firstTitle = "电影";
                $this->secondTitle = "MOVIE NEWS";
                break;
            case 1007: //电视
                $this->firstTitle = "电视";
                $this->secondTitle = "TV NEWS";
                break;
            case 1008: //音乐
                $this->firstTitle = "音乐";
                $this->secondTitle = "MUSIC NEWS";
                break;
            case 1009: //综艺
                $this->firstTitle = "综艺";
                $this->secondTitle = "VARIETY NEWS";
                break;
        }

        if ($this->recordCount == null || trim($this->recordCount) == "")
            $this->recordCount = 5;
        if ($this->currentPageNumber == null)
            $this->currentPageNumber = 1;
        $currentRowNumber = ($this->currentPageNumber - 1) * $this->recordCount;
        $sqlStr = "select count(*) as rowsCount from news where status_id=1005 ";
        if ($this->catalogId != null)
            $sqlStr = $sqlStr . " and catalog_id=" . $this->catalogId;
        if ($this->subCatalogId != null)
            $sqlStr = $sqlStr . " and sub_catalog_id=" . $this->subCatalogId;
        $rowNumber = $this->getDataRows($sqlStr);
        $rowNumber = $rowNumber[0]["rowsCount"];
        if ($rowNumber % $this->recordCount == 0)
            $this->totalPageNumber = $rowNumber / $this->recordCount;
        else
        {
            $tempStr = $rowNumber / $this->recordCount;
            $tempStr = substr($tempStr, 0, strrpos($tempStr, ".", 0));
            $this->totalPageNumber = $tempStr + 1;
        }

        //        $sqlStr = "with newQuery as(select row_number() over(order by publish_time desc, news_id desc) as rowNumber, name, news_id, cast(title as varchar(100)) title,short_comment, cast(brief_title as varchar(200)) brief_title, (case when brief is null then body  when brief='' then body else brief end) as brief, cast(snapshot_big as varchar(100)) snapshot_big, publish_time from news n inner join Catalog c on n.catalog_id = c.catalog_id where status_id=1005";
        //
        //
        //        if (isset($this->catalogId) == true)
        //            $sqlStr = $sqlStr . " and n.catalog_id=" . $this->catalogId;
        //        if ($this->subCatalogId != null)
        //            $sqlStr = $sqlStr . " and sub_catalog_id=" . $this->subCatalogId;
        //
        //        $sqlStr = $sqlStr . ") select top " . $this->recordCount . " * from newQuery where rowNumber > " . $currentRowNumber;


        $sqlStr = "select news_id,title,short_comment,brief_title,(case when brief is null then body  when brief='' then body else brief end) as brief,snapshot_big, publish_time from news n inner join Catalog c on n.catalog_id = c.catalog_id where status_id=1005";
        if (isset($this->catalogId) == true)
            $sqlStr = $sqlStr . " and n.catalog_id=" . $this->catalogId;
        if ($this->subCatalogId != null)
            $sqlStr = $sqlStr . " and sub_catalog_id=" . $this->subCatalogId;
        $sqlStr = $sqlStr . " order by publish_time desc limit " . $currentRowNumber . "," . $this->recordCount;

        $this->newsArray = array();
        $this->newsArray = $this->getDataRows($sqlStr);

        //        $sqlStr = "select top " .$this->recordCount. " name, news_id, cast(title as varchar(100)) title,short_comment, cast(brief_title as varchar(200)) brief_title, cast(brief as varchar(500)) brief, cast(snapshot_big as varchar(100)) snapshot_big, datediff(hour,create_time,GETDATE()) as currentHour from news n inner join Catalog c on n.catalog_id = c.catalog_id where status_id=1005";
        //	    if(isset($this->catalogId) == true)
        //	       $sqlStr = $sqlStr." and n.catalog_id=".$this->catalogId;
        //        $sqlStr = $sqlStr." and sub_catalog_id=".$this->subCatalogId." order by publish_time desc" ;
        //        $this->newsArray=$this->getDataRows($sqlStr);
        //        $this->recordCount = count($this->newsArray);
    }

    public function executeTestMethod($request)
    {
        //        $this->page = new sfDoctrinePager("News",2);
        //        $this->page->setQuery("select * from news");
        //        $this->page->setPage($request->getParameter("page"),1);
        //        $this->page->init();
        $query = Doctrine_Query::create()
                ->from("news n")
                ->addFrom("FocusContent f")
                ->where("f.content_type_i = ?", 1000);
        //->orderBy("f.order_ids desc")
        //->addPendingJoinCondition("f","f.content_idty=n.news_id");
        //->orderBy("f.order_i desc");

        $this->page = new sfDoctrinePager('FocusContent', sfConfig::get("app_record_count"));
        $this->page->setQuery($query); //$q   数据库查询语句
        $this->page->setPage($request->getParameter('page', 1));
        $this->page->init();
        //        $q = Doctrine_Query::create()
        //                ->from("")
        //                ->addPendingJoinCondition()


    }


    function getDataRows($sqlStr)
    {
        $dbBase = new DbBase();
        return $dbBase->getDataQuery($sqlStr);
    }

    public function executePageNation(sfWebRequest $request)
    {

        if ($this->rowCount == null || trim($this->rowCount) == "")
            $this->rowCount = 5;
        if ($this->currentPageNumber == null)
            $this->currentPageNumber = 1;
        $currentRowNumber = ($this->currentPageNumber - 1) * $this->rowCount;
        $sqlStr = "select count(*) as rowsCount from news where status_id = 1005";
        $rowNumber = $this->getDataRows($sqlStr);
        $rowNumber = $rowNumber[0]['rowsCount'];
        if ($rowNumber % $this->rowCount == 0)
            $this->totalPageNumber = $rowNumber / $this->rowCount;
        else
        {
            $tempStr = $rowNumber / $this->rowCount;
            $tempStr = substr($tempStr, 0, strrpos($tempStr, ".", 0));
            $this->totalPageNumber = $tempStr + 1;
        }
        $sqlStr = "with newQuery as(select row_number() over(order by publish_time desc) as rowNumber, * from news where status_id = 1005) select top " . $this->rowCount . " * from newQuery where rowNumber > " . $currentRowNumber;
        $this->newsArray = $this->getDataRows($sqlStr);
    }

}
